﻿@page "/Form4"
@using System.Diagnostics.CodeAnalysis
@using BootstrapBlazor.OnlyServerZhongShun.Data.dbmodels
@using System.Data
@inject ILogger<Form4> NormalLogger
@inject AuthenticationStateProvider authenticationStateProvider
@inject ToastService ToastService
@inject IConfiguration _congifuration
@inject SqlSugarScope _SqlSugarScope
@attribute [Authorize(Roles = "超级管理员,业务总, 业务经理, 业务员,不含回款数据")]

    <style>
        .radio-list {
            --bb-radio-item-width: 70px;
        }

        .checkbox-list {
            --bb-checkbox-item-width: 80px;
        }
        .multi-select {
            --bb-multi-select-min-height: 26px;
        }
    </style>
<div class="row g-1">
    <div class="col-4">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="查询日期" />
            <DateTimeRange @bind-Value="@NormalDateTimeRangeValue" OnConfirm="OnNormalConfirm" ShowSidebar="true" ViewMode="DatePickerViewMode.Date" IsEditable="true" DateFormat="yyyy-MM-dd">
                <TimePickerSetting ShowClockScale="true" IsAutoSwitch="false" />
            </DateTimeRange>
        </BootstrapInputGroup>
    </div>
    <div class="col-3">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="客户" />
            <Select IsVirtualize="true" Items="客户列表Items" @bind-Value="选中客户" OnSearchTextChanged="OnSearchTextChanged" ShowSearch="true" IsClearable="true"></Select>
        </BootstrapInputGroup>
    </div>
    <div class="col-3">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="业务员" />
            <MultiSelect Items="@业务员Items" @bind-Value="@选中业务员List" OnSelectedItemsChanged="@业务员选择改变事件" ShowToolbar="true" />
        </BootstrapInputGroup>
    </div>
    <div class="col-2">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="养殖类别" />
            <Select IsVirtualize="true" Items="养殖类别Items" @bind-Value="选中养殖类别" ShowSearch="false" IsClearable="true"></Select>
        </BootstrapInputGroup>
    </div>
</div>
<div class="row g-1">
    <div class="col-md-4">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="部门" />
            <MultiSelect Items="@部门Items" @bind-Value="@选中部门List" OnSelectedItemsChanged="@部门选择改变事件" ShowToolbar="true" />
        </BootstrapInputGroup>
    </div>
    <div class="col-md-4">
        <CheckboxList  TValue="string" @bind-Value="@选中查询列" Items="@查询列" ShowLabel="false" ShowLabelTooltip="true"></CheckboxList>
    </div>
    <div class="col-2">
        <RadioList TValue="string" Items="@查询类别" @bind-Value="@选中查询类别" Class="custom-radio4" />
    </div>

    <div class="col-md-2">
        <Button OnClick="@Button_chaxun_Click" IsAsync="true" Color="Color.Primary">查询</Button>
        <Button OnClick="@Button_daochu" Color="Color.Info">导出</Button>

        </div>
</div>


    <Table Class="form4table" TItem="DynamicObject" DynamicContext="DataTableDynamicContext"
           IsBordered="true" IsMultipleSelect="false" ShowToolbar="true" ShowRefresh="false" ShowDefaultButtons="false" IsPagination="true" ShowExportButton="false"
           OnExportAsync="OnExportAsync" OnSort="OnSort" ShowFooter="true" ShowLoading="true" IsStriped="true"
           PageItemsSource="new int[]{1000,2000,3000,4000,5000,10000}" TableSize="TableSize.Compact" 
       Height="500"  IsFixedHeader="true" >
        
    <FooterTemplate>
        <tr>
            @if (DataTableDynamicContext != null && DataTableDynamicContext.DataTable.Rows.Count > 0)
            {
                @foreach (DataColumn property in DataTableDynamicContext.DataTable.Columns)
                {
                    bool visible = DataTableDynamicContext.GetColumns().FirstOrDefault(i => i.GetFieldName() == property.ColumnName)?.Visible ?? true;
                    @if (visible == false)
                    {
                        continue;
                    }
                    <td>
                        @*  //如果不是 代理商名称,部门 列 *@
                        @* @if (property.ColumnName != "代理商名称" && property.ColumnName != "部门" && property.ColumnName != "销售员" && Type.GetTypeCode(property.DataType) == TypeCode.String) *@
                            @* 当前列是否可见 *@

                        @if (Type.GetTypeCode(property.DataType) != TypeCode.String && property.ColumnName != "退货率" && visible && property.ColumnName != "序号")
                        {
                            //当前列的合计
                            var sum = DataTableDynamicContext.DataTable.AsEnumerable().Sum(dr =>
                            {
                                if (dr.IsNull(property.ColumnName)) return 0m;
                                object value = dr[property.ColumnName];
                                if (value is int)
                                    return (decimal)(int)value;
                                else if (value is decimal)
                                    return (decimal)value;
                                else
                                    return 0m;
                            });
                            @sum
                        }
                        else if (property.ColumnName == "退货率")
                        {
                            //销售差异
                            var sum1 = DataTableDynamicContext.DataTable.AsEnumerable().Sum(dr =>
                            {
                                if (dr.IsNull("退货额合计")) return 0m;
                                object value = dr["退货额合计"];
                                if (value is int)
                                    return (decimal)(int)value;
                                else if (value is decimal)
                                    return (decimal)value;
                                else
                                    return 0m;
                            });
                            //同期销售额
                            var sum2 = DataTableDynamicContext.DataTable.AsEnumerable().Sum(dr =>
                            {
                                if (dr.IsNull("发货净额合计")) return 0m;
                                object value = dr["发货净额合计"];
                                if (value is int)
                                    return (decimal)(int)value;
                                else if (value is decimal)
                                    return (decimal)value;
                                else
                                    return 0m;
                            });
                            if (sum2 != 0)
                            {
                                @((sum1 / sum2).ToString("p2"))
                            }
                        }
                        else if (property.ColumnName == "序号")
                        {
                            @:合计
                        }
                        else
                        {
                            @:--
                        }

                    </td>
                }
            }

        </tr>
    </FooterTemplate>
</Table>


@code {
    private string username;

    [Inject]
    [NotNull]
    private ITableExport? TableExport { get; set; }
    [Inject]
    [NotNull]
    private ToastService? Toast { get; set; }

    private List<View_Custom> View_CustomList = new List<View_Custom>();
    private List<View_Depart> View_DepartList = new List<View_Depart>();
    //时间范围
    private DateTimeRangeValue NormalDateTimeRangeValue { get; set; } = new();

    private IEnumerable<SelectedItem> 客户列表Items => View_CustomList.Select(i => new SelectedItem(i.cCusCode!, i.cCusName!)).ToList();
    private IEnumerable<SelectedItem> 部门Items => View_DepartList.Select(i => new SelectedItem(i.cDepCode!, i.cDepName!)).ToList();

     private List<View_Custom> view_Customs = new List<View_Custom>();
    private SelectedItem? 选中养殖类别 { get; set; }
    private IEnumerable<SelectedItem> 养殖类别Items => view_Customs.Select(i => new SelectedItem(i.养殖类别!, i.养殖类别!)).ToList();


    [NotNull]
    private IEnumerable<SelectedItem>? 查询列 { get; set; } = new List<SelectedItem>(new List<SelectedItem>
    {
        new SelectedItem { Text = "产品", Value = "产品名称" },
         new SelectedItem { Text = "类别", Value = "类别" },
        new SelectedItem { Text = "客户", Value = "代理商名称"},
        new SelectedItem { Text = "业务员", Value = "销售员" },
        // new SelectedItem { Text = "数量", Value = "数量" },
        // new SelectedItem { Text = "金额", Value = "金额" },
    });

    [NotNull]
    private IEnumerable<SelectedItem>? 查询类别 { get; set; } = new List<SelectedItem>(new List<SelectedItem>
    {
        new SelectedItem { Text = "金额", Value = "0" },
        new SelectedItem { Text = "数量", Value = "1"}
    });

    private string 选中查询类别 { get; set; }
    private SelectedItem? 选中客户 { get; set; }
    private IEnumerable<string> 选中部门List { get; set; }
    private string 选中部门InStr = "";
    private string 选中查询列 { get; set; }

    private List<View_CustomPerson> View_CustomPersonList = new List<View_CustomPerson>();
    private IEnumerable<string> 选中业务员List { get; set; }
    private string 选中业务员InStr = "";
    private IEnumerable<SelectedItem> 业务员Items => View_CustomPersonList.Select(i => new SelectedItem(i.cCusPPerson!, i.cPersonName!)).ToList();


    //表格数据
    [NotNull]
    private DataTableDynamicContext? DataTableDynamicContext { get; set; }

    protected override async void OnInitialized()
    {

        var authState = await authenticationStateProvider.GetAuthenticationStateAsync();
        var user = authState.User;
        username = user.Identity.Name;



        //今年1月1日
        NormalDateTimeRangeValue.Start = new DateTime(DateTime.Now.Year, 1, 1);
        //今天
        NormalDateTimeRangeValue.End = DateTime.Now.AddDays(-1);

        View_CustomList = _SqlSugarScope.Queryable<View_Custom>().ToList();
        //加一个请选择
        View_CustomList.Insert(0, new View_Custom { cCusCode = "", cCusName = "请选择" });
        List<View_Custom> cus = _SqlSugarScope.Queryable<View_Custom>().Where(it => !string.IsNullOrEmpty(it.养殖类别)).Distinct().Select(it => new View_Custom { 养殖类别 = it.养殖类别 }).ToList();
        view_Customs = cus;
        view_Customs.Insert(0, new View_Custom { 养殖类别 = "请选择" });
        view_Customs.Add(new View_Custom { 养殖类别 = "非小棚虾" });
        //读取配置文件
        UserDataAuth userDataAuth = _SqlSugarScope.Queryable<UserDataAuth>().Where(it => it.UserName == username).First();
        List<Guid> guids = userDataAuth.CanViewDepart.Split(",").ToList().Select(it => Guid.Parse(it)).ToList();
        List<Sys_Department> depts = _SqlSugarScope.Queryable<Sys_Department>().Where(it => guids.Contains(it.DepartmentId)).ToList();
        List<string> deptNames = depts.Select(it => it.DepartmentName).ToList();
        var PaiChuBuMen = _congifuration.GetSection("PaiChuBuMen").Value;
        List < View_Depart > deptss = _SqlSugarScope.Queryable<View_Depart>().Where($"cDepCode not in ({PaiChuBuMen})").ToList();
        View_DepartList = deptss.Where(it => deptNames.Contains(it.cDepName)).ToList();
        //读取业务员配置文件
        var PaiChuYeWuYuan = _congifuration.GetSection("PaiChuYeWuYuan").Value;
        string sql = $"select distinct cPersonName,cCusPPerson from View_CustomPerson where cCusPPerson not in ({PaiChuYeWuYuan})";
        View_CustomPersonList = _SqlSugarScope.Ado.SqlQuery<View_CustomPerson>(sql);
    }
    private Task OnNormalConfirm(DateTimeRangeValue value)
    {
        NormalLogger.LogDebug($"选择的时间范围是: {value.Start:yyyy-MM-dd HH:mm:ss} - {value.End:yyyy-MM-dd HH:mm:ss}");
        //遍历输出选中的客户
        if (选中客户 != null)
        {
            NormalLogger.LogDebug($"选中的客户是: {选中客户.Text}");
        }
        //遍历输出选中的部门
        if (选中部门List != null)
        {
            foreach (var item in 选中部门List)
            {

            }
        }
        return Task.CompletedTask;
    }
    private IEnumerable<SelectedItem> OnSearchTextChanged(string searchText)
    {
        return View_CustomList.Where(i => i.cCusName!.Contains(searchText, StringComparison.OrdinalIgnoreCase)).Select(i => new SelectedItem(i.cCusCode!, i.cCusName!));
    }

    private Task 部门选择改变事件(IEnumerable<SelectedItem> items)
    {
        选中部门InStr = string.Join("','", items.Select(it => it.Text).ToArray());
        return Task.CompletedTask;
    }

    private Task 业务员选择改变事件(IEnumerable<SelectedItem> items)
    {
        选中业务员InStr = string.Join("','", items.Select(it => it.Text).ToArray());
        return Task.CompletedTask;
    }

    private async Task<bool> OnExportAsync(ITableExportDataContext<DynamicObject> context)
    {
        // 导出符合条件的所有数据 data
        var ret = await TableExport.ExportExcelAsync(DataTableDynamicContext.GetItems(), context.Columns, $"Test_{DateTime.Now:yyyyMMddHHmmss}.xlsx");

        // 返回 true 时自动弹出提示框
        // await ShowToast(ret);
        return ret;
    }

    private async void Button_daochu(MouseEventArgs e)
    {
        //复制一个table用来导出
        DataTable dt = DataTableDynamicContext.DataTable.Copy();
        //增加一行合计
        DataRow dr = dt.NewRow();
        dr["发货额合计"] = dt.AsEnumerable().Sum(dr =>
                                {
                                    if (dr.IsNull("发货额合计")) return 0m;
                                    object value = dr["发货额合计"];
                                    if (value is int)
                                        return (decimal)(int)value;
                                    else if (value is decimal)
                                        return (decimal)value;
                                    else
                                        return 0m;
                                });
        dr["退货额合计"] = dt.AsEnumerable().Sum(dr =>
        {
            if (dr.IsNull("退货额合计")) return 0m;
            object value = dr["退货额合计"];
            if (value is int)
                return (decimal)(int)value;
            else if (value is decimal)
                return (decimal)value;
            else
                return 0m;
        });
        dr["质量退货合计"] = dt.AsEnumerable().Sum(dr =>
        {
            if (dr.IsNull("质量退货合计")) return 0m;
            object value = dr["质量退货合计"];
            if (value is int)
                return (decimal)(int)value;
            else if (value is decimal)
                return (decimal)value;
            else
                return 0m;
        });
        dr["发货净额合计"] = dt.AsEnumerable().Sum(dr =>
        {
            if (dr.IsNull("发货净额合计")) return 0m;
            object value = dr["发货净额合计"];
            if (value is int)
                return (decimal)(int)value;
            else if (value is decimal)
                return (decimal)value;
            else
                return 0m;
        });

        dr["退货率"] = (decimal)dr["发货净额合计"] == 0 ? 0 : (decimal)dr["退货额合计"] / (decimal)dr["发货净额合计"];
        for (int i = 1; i < 13; i++)
        {
            dr[i+"月发货额"] = dt.AsEnumerable().Sum(dr =>
        {
            if (dr.IsNull(i + "月发货额")) return 0m;
            object value = dr[i + "月发货额"];
            if (value is int)
                return (decimal)(int)value;
            else if (value is decimal)
                return (decimal)value;
            else
                return 0m;
        });
        }

        dt.Rows.Add(dr);
        DataTableDynamicContext dataTable = new DataTableDynamicContext(dt);
        var ret = await TableExport.ExportExcelAsync(dataTable.GetItems(), DataTableDynamicContext.GetColumns().Where(it => it.Visible != false), $"发货有效率分析表_{DateTime.Now:yyyyMMddHHmmss}.xlsx");


        // 导出符合条件的所有数据 data
        // var ret = await TableExport.ExportExcelAsync(DataTableDynamicContext.GetItems(), DataTableDynamicContext.GetColumns().Where(it => it.Visible != false), $"代理发货有效率分析_{DateTime.Now:yyyyMMddHHmmss}.xlsx");
        ToastService.Success("提示", "导出成功");
    }

    private string OnSort(string sortName, SortOrder sortOrder)
    {

        string sortString = "";
        if (sortOrder == SortOrder.Asc)
        {
            sortString = $"{sortName} asc";
        }
        else if (sortOrder == SortOrder.Desc)
        {
            sortString = $"{sortName} desc";
        }
        else
        {
            sortString = $"{sortName}";
        }
        DataTable sortTable = DataTableDynamicContext.DataTable.Clone();

        DataView dv = DataTableDynamicContext.DataTable.DefaultView;
        dv.Sort = sortString;
        sortTable = dv.ToTable();

        显示数据(sortTable);
        return sortString;
    }

    private decimal sum1;
    private decimal sum2;
    private decimal sum3;
    private decimal sum4;
    private decimal sum5;
    private decimal sum6;
    private decimal sum7;
    private decimal sum8;

    private async void Button_chaxun_Click(MouseEventArgs e)
    {
        //客户发货排名
        string sql;
        string 年度 = this.NormalDateTimeRangeValue.End.Year.ToString();
        string 今年开始日期 = this.NormalDateTimeRangeValue.Start.ToString("yyyy-MM-dd");
        string 今年结束日期 = this.NormalDateTimeRangeValue.End.ToString("yyyy-MM-dd");
        string 去年开始日期 = this.NormalDateTimeRangeValue.Start.AddYears(-1).ToString("yyyy-MM-dd");
        string 去年结束日期 = this.NormalDateTimeRangeValue.End.AddYears(-1).ToString("yyyy-MM-dd");
        string 去年 = 去年开始日期.Substring(0, 4);
        string 今年sql;
        string 去年sql;
        // string loginuser = username;
        string loginuser = "admin";
        sql = "SELECT DepartmentName FROM [netcoredev].[dbo].[View_PersonDepart] where  cPersonName='" + loginuser + "'";
        string depart = _SqlSugarScope.Ado.GetString(sql);
        string guid = Guid.NewGuid().ToString();  ///本次查询统一的
        if (string.IsNullOrEmpty(选中部门InStr) == true)
        {
            选中部门InStr += string.Join("','", View_DepartList.Select(it => it.cDepName));
        }
        string 部门清单 = "('" + 选中部门InStr + "')";

        sql = "select docyear, dbname FROM      Tset where docyear='" + 年度 + "'";
        DataTable tb1 = _SqlSugarScope.Ado.GetDataTable(sql);
        if (tb1.Rows.Count > 0)
        {
            今年sql = tb1.Rows[0]["dbname"].ToString();
            sql = "delete T代理发货有效率分析 where usercode='" + loginuser + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            sql = "delete T代理发货有效率分析显示 where usercode='" + loginuser + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            //处理今年(今年sql)
            //1,塞入空客户
            #region
            sql = @"insert into  T代理发货有效率分析 (guid, usercode,Depart,cCusCode,cDLCode,irowno,cPersonCode,dDate,cInvCode,cwhcode,  发货数量, 发货金额, 退货数量,
                退货金额,质量退货金额,质量退货数量)";
            sql += "select '" + guid + "','" + loginuser + "','" + depart + "', cCusCode ,SBVID,irowno,cPersonCode,dDate,";
            sql += " cInvCode,cwhcode,0,0,0,0,0,0 from View_发票明细 where 1=1 and period='" + 年度 + "'";
            if (loginuser != "admin")
            {
                sql += " and cCusCode in (SELECT  cCusCode FROM View_CustomDepart where  cDepName='" + depart + "')";
                sql += " and cCusCode in (SELECT  cCusCode FROM TPersonCustom where cPersonName='" + loginuser + "')";
            }
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            #endregion
            //开始更新本期销售额（正数)
            #region
            sql = " update a set a.发货数量=b.iQuantity,a.发货金额=b.iMoney   ";
            sql += " from  T代理发货有效率分析 a ,View_发票明细 b ";
            sql += " where a.guid='" + guid + "' and a.cDLCode=b.SBVID and a.irowno=b.irowno and a.[dDate]=b.[dDate] ";
            sql += " and b.period='" + 年度 + "' and b.dDate<='" + 今年结束日期 + "'and b.dDate>='" + 今年开始日期 + "'";
            //销售还是退货 按仓库区分。金额去掉
            // sql += "   and b.iMoney >0";
            //----------发货额 成品库+调拨库
            sql += " and b.cwhcode in ('03','10','14')";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            #endregion
            //开始更新本期销售额（负数)
            #region
            sql = " update a set a.退货数量=b.iQuantity,a.退货金额=b.iMoney   ";
            sql += " from  T代理发货有效率分析 a ,View_发票明细 b ";
            sql += " where a.guid='" + guid + "' and a.cDLCode=b.SBVID and a.irowno=b.irowno and a.[dDate]=b.[dDate] ";
            sql += " and b.period='" + 年度 + "' and b.dDate<='" + 今年结束日期 + "'and b.dDate>='" + 今年开始日期 + "'";
            //销售还是退货 按仓库区分。金额去掉
            // sql += " and b.iMoney <0";
            //----------退货取退货库
            sql += " and b.cwhcode in ('06')";
            _SqlSugarScope.Ado.ExecuteCommand(sql);

            //开始更新质量退货合计
            sql = " update a set a.质量退货数量=b.iQuantity,a.质量退货金额=b.iMoney   ";
            sql += " from  T代理发货有效率分析 a ,View_发票明细 b ";
            sql += " where a.guid='" + guid + "' and a.cDLCode=b.SBVID and a.irowno=b.irowno and a.[dDate]=b.[dDate] ";
            sql += " and b.period='" + 年度 + "' and b.dDate<='" + 今年结束日期 + "'and b.dDate>='" + 今年开始日期 + "' ";
            //销售还是退货 按仓库区分。金额去掉
            // sql += " and b.iMoney <0";
            //----------退货取退货库
            sql += " and b.cwhcode in ('017')";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            #endregion
            //更新客户
            sql = "update  a set a.代理商名称=b.cCusName ,a.销售员=b.cPersonName,a.cPersonCode=b.cCusPPerson ";
            sql += " from T代理发货有效率分析 a, View_CustomPerson b where a.cCusCode=b.cCusCode and a.guid='" + guid + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            //更新销售员
            // sql = "update  a set a.销售员=b.cPersonName ";
            // sql += " from T代理发货有效率分析 a, View_Person b where a.cPersonCode=b.cPersonCode and a.guid='" + guid + "'";
            // _SqlSugarScope.Ado.ExecuteCommand(sql);
            //更新部门
            sql = "update  a set a.部门=b.DepartmentName ";
            sql += " from T代理发货有效率分析 a, View_PersonDepart b where a.cPersonCode=b.cPersonCode and a.guid='" + guid + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);

            //更新产品
            sql = "update  a set a.产品名称=b.cInvName,a.规格=b.cInvStd,a.类别=b.类别名称 ";
            sql += " from T代理发货有效率分析 a, View_Inventory b where a.cInvCode=b.cInvCode and a.guid='" + guid + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            //更新月度
            sql = "update  T代理发货有效率分析 set  月度= MONTH(  convert(date, [dDate]))  where  guid='" + guid + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            //养殖类别
            sql = "update  a set a.养殖类别=b.养殖类别 ";
            sql += " from T代理发货有效率分析 a, View_Custom b where  a.cCusCode=b.cCusCode and a.guid='" + guid + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            //显示数据
            sql = "delete T代理发货有效率分析显示 where  guid='" + guid + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
            for (int i = 1; i <= 12; i++)
            {
                ///塞入显示表后再显示
                sql = "insert into T代理发货有效率分析显示(guid, usercode, Depart, cCusCode, cDLCode, cPersonCode, irowno, dDate, cInvCode, cwhcode, ";
                sql += " 产品名称, 规格, 类别, 部门, 销售员, 代理商名称, ";
                sql += " 发货数量" + i.ToString() + ", 发货金额" + i.ToString() + ", 退货数量" + i.ToString() + ", 退货金额" + i.ToString() + ",质量退货金额" + i.ToString() + ",质量退货数量" + i.ToString() + ")";
                sql += " select guid, usercode, Depart, cCusCode, cDLCode, cPersonCode, irowno, dDate, cInvCode, cwhcode, ";
                sql += " 产品名称, 规格, 类别, 部门, 销售员, 代理商名称,";
                sql += " 发货数量, 发货金额, 退货数量, 退货金额,质量退货金额,质量退货数量  FROM    T代理发货有效率分析 where  guid='" + guid + "' and 月度='" + i + "' ";
             
                if (this.选中养殖类别?.Text != "请选择" && this.选中养殖类别?.Text != "非小棚虾")
                {
                    sql += " and 养殖类别='" + this.选中养殖类别.Text + "'";
                }
                if (this.选中养殖类别?.Text != "请选择" && this.选中养殖类别?.Text == "非小棚虾")
                {
                    sql += " and 养殖类别 is null";
                }
                _SqlSugarScope.Ado.ExecuteCommand(sql);
            }
            // if (选中查询类别 != null && 选中查询类别.Split(",").Contains("产品") == true)
            // {
            //     sql = @" select    产品名称,规格 ";
            // }
            // else
            // {
            //     sql = @" select    类别,规格 ";
            // }
            // if (选中查询类别 != null && 选中查询类别.Split(",").Contains("类别") == true)
            // {
            //     sql = @" select    类别,规格 ";
            // }
            // else
            // {
            //     sql = @" select    产品名称,规格 ";
            // }
            // if (选中查询类别 != null && 选中查询类别.Split(",").Contains("客户") == true)
            // {
            //     sql += ",代理商名称";
            // }
            // if (选中查询类别 != null && 选中查询类别.Split(",").Contains("业务员") == true)
            // {
            //     sql += ",销售员";
            // }

            if (string.IsNullOrEmpty(选中查询列) == false)
            {
                // s1 = 选中查询列.Replace("数量,", "").Replace("数量", "").Replace("金额,", "").Replace("金额", "");
                sql = "select " + 选中查询列.TrimEnd(',') + ",0.0 发货额合计,0.0 退货额合计, 0.0 质量退货合计,0.0 发货净额合计,0.0 退货率,";
            }
            else
            {
                sql = "select  产品名称,类别,代理商名称,销售员,0.0 发货额合计,0.0 退货额合计,0.0 质量退货合计, 0.0 发货净额合计,0.0 退货率,";
            }

            string s1 = "";
            //显示数据
            switch (选中查询类别)
            {
                case "0":
                    {
                        for (int k = 1; k <= 12; k++)
                        {
                            sql += "Convert(decimal(18,4),sum(isnull(发货金额" + k + ",0))/10000)  '" + k + "月发货额',";
                        }
                        for (int k = 1; k <= 12; k++)
                        {
                            sql += "  Convert(decimal(18,4),sum(isnull(退货金额" + k + ",0))/10000) '" + k + "月退货金额'" + ",Convert(decimal(18,4),(sum(isnull(发货金额" + k + ",0))+sum(isnull(退货金额" + k + ",0)))/10000) '" + k + "月净金额'" + ",";

                            sql += " Convert(decimal(18,4),sum(isnull(质量退货金额" + k + ",0))/10000) '" + k + "月质量退货金额'" + ",";
                            }
                        break;
                    }
                default:
                    {
                        for (int k = 1; k <= 12; k++)
                        {
                            sql += "sum(isnull(发货数量" + k + ",0)) '" + k + "月发货额'" + ",sum(isnull(退货数量" + k + ",0)) '" + k + "月退货金额'" + ",sum(isnull(发货数量" + k + ",0))+sum(isnull(退货数量" + k + ",0)) '" + k + "月净金额'" + ",";
                            sql += "sum(isnull(质量退货数量" + k + ",0)) '" + k + "月质量退货金额'" + ",";
                        }
                        break;
                    }
            }
                    //取到sql最后一个逗号
                    // sql = sql.TrimEnd(',');
                    sql += " 0.0 发货数量总计,0.0 发货金额总计 FROM T代理发货有效率分析显示 where guid='" + guid + "'  ";
                    if (string.IsNullOrEmpty(this.选中客户?.Text) == false && this.选中客户?.Text != "请选择")
                    {
                        sql += " and 代理商名称 like '%" + this.选中客户.Text + "%'";
                    }

                     if (string.IsNullOrEmpty(选中业务员InStr) == false )
                    {
                    sql += " and 销售员 in ('" + 选中业务员InStr + "')";
                    }

                    sql += " and 部门 in " + 部门清单;


                        if (string.IsNullOrEmpty(选中查询列) == false)
                    {
                     sql += "group by " + 选中查询列.TrimEnd(',');
                    }
                    else
                    {
                        sql += "group by 产品名称,类别,代理商名称,销售员";
                    }

                    // sql += " group by 产品名称,规格 ";
                    // if (选中查询类别 != null && 选中查询类别.Split(",").Contains("产品+类别") == true)
                    // {
                    //     sql += ",类别";
                    // }
                    // if (选中查询类别 != null && 选中查询类别.Split(",").Contains("客户") == true)
                    // {
                    //     sql += ",代理商名称";
                    // }
                    // if (选中查询类别 != null && 选中查询类别.Split(",").Contains("业务员") == true)
                    // {
                    //     sql += ",销售员";
                    // }


                    DataTable dataTableHZ = _SqlSugarScope.Ado.GetDataTable(sql);

                    sql = " delete T代理发货有效率分析显示 where  guid='" + guid + "' ";
                    sql += " delete T代理发货有效率分析 where  guid='" + guid + "' ";
                    _SqlSugarScope.Ado.ExecuteCommand(sql);

                    显示数据(dataTableHZ);
                    await ToastService.Information("提示", "查询成功");
           
        }
        else
        {
            await ToastService.Success("提示", "选择的日期有误", autoHide: true);
        }

    }

    public void 显示数据(DataTable dt)
    {


        if (dt != null && dt.Rows.Count > 0)
        {
            if (dt.Columns.Contains("销售员"))
            {
                dt.Columns["销售员"].ColumnName = "业务员";
            }
            if (dt.Columns.Contains("代理商名称"))
            {
                dt.Columns["代理商名称"].ColumnName = "客户名称";
            }
            // //找出对应选中的销售员
            // if (选中业务员InStr != "" && dt.Columns.Contains("业务员"))
            // {
            //     dt = dt.Select("业务员 in ('" + 选中业务员InStr + "')").CopyToDataTable();
            // }
            //计算每一行的合计
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                decimal sum1 = 0;
                decimal sum2 = 0;
                decimal sum3 = 0;
                decimal sum4 = 0;
                decimal sum5 = 0;
                decimal sum6 = 0;
                decimal sum7 = 0;
                decimal sum8 = 0;
                decimal sum质量退货数量 = 0;
                decimal sum质量退货金额 = 0;
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (dt.Columns[j].ColumnName.Contains("月发货数量") == true)
                    {
                        sum1 += Convert.ToDecimal(dt.Rows[i][j]);
                    }
                    if (dt.Columns[j].ColumnName.Contains("月发货额") == true)
                    {
                        sum2 += Convert.ToDecimal(dt.Rows[i][j]);
                    }
                    if (dt.Columns[j].ColumnName.Contains("月退货数量") == true)
                    {
                        sum3 += Convert.ToDecimal(dt.Rows[i][j]);
                    }
                    if (dt.Columns[j].ColumnName.Contains("月退货金额") == true)
                    {
                        sum4 += Convert.ToDecimal(dt.Rows[i][j]);
                    }
                    if (dt.Columns[j].ColumnName.Contains("月净数量") == true)
                    {
                        sum5 += Convert.ToDecimal(dt.Rows[i][j]);
                    }
                    if (dt.Columns[j].ColumnName.Contains("月净金额") == true)
                    {
                        sum6 += Convert.ToDecimal(dt.Rows[i][j]);
                    }

                    if (dt.Columns[j].ColumnName.Contains("月质量退货数量") == true)
                    {
                        sum质量退货数量 += Convert.ToDecimal(dt.Rows[i][j]);
                    }
                    if (dt.Columns[j].ColumnName.Contains("月质量退货金额") == true)
                    {
                        sum质量退货金额 += Convert.ToDecimal(dt.Rows[i][j]);
                    }

                }
                dt.Rows[i]["发货额合计"] = sum2;
                dt.Rows[i]["退货额合计"] = sum4;
                dt.Rows[i]["质量退货合计"] = sum质量退货金额;
                dt.Rows[i]["发货净额合计"] = sum2 + sum4 + sum质量退货金额;
                dt.Rows[i]["发货数量总计"] = sum5;
                dt.Rows[i]["发货金额总计"] = sum6;
                if (sum6 != 0)
                {
                    dt.Rows[i]["退货率"] = sum4 / sum6;
                }      
                // dt.Rows[i]["退货总计"] = sum3;
                // dt.Rows[i]["退货金额总计"] = sum4;
                // dt.Rows[i]["净数量总计"] = sum5;
                // dt.Rows[i]["净金额总计"] = sum6;
            }
            //移除 退货金额  净金额
            // for (int i = 1; i <= 12; i++)
            // {
            //     //存在就删除
            //     if (dt.Columns.Contains(i+"月退货金额") == true)
            //     {
            //         dt.Columns.Remove(i + "月退货金额");
            //     }
            //     if (dt.Columns.Contains(i + "月净金额") == true)
            //     {
            //         dt.Columns.Remove(i + "月净金额");
            //     }
            //     if (dt.Columns.Contains(i + "月发货数量") == true)
            //     {
            //         dt.Columns.Remove(i + "月发货数量");
            //     }
            //     if (dt.Columns.Contains(i + "月退货数量") == true)
            //     {
            //         dt.Columns.Remove(i + "月退货数量");
            //     }
            //     if (dt.Columns.Contains(i + "月净数量") == true)
            //     {
            //         dt.Columns.Remove(i + "月净数量");
            //     }

            //     if (dt.Columns.Contains("发货数量总计") == true)
            //     {
            //         dt.Columns.Remove("发货数量总计");
            //     }
            //     if (dt.Columns.Contains("发货金额总计") == true)
            //     {
            //         dt.Columns.Remove("发货金额总计");
            //     }
            // }
            //修改列名
            if (dt.Columns.Contains("序号") == false)
            {
                dt.Columns.Add("序号", typeof(int)).SetOrdinal(0);
            }
   
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dt.Rows[i]["序号"] = i + 1;
            }
        }

        List<string> columnList = new List<string>();
        if (dt != null)
        {
            foreach (DataColumn column in dt.Columns)
            {
                if (column.ColumnName == "产品名称" || column.ColumnName == "规格" || column.ColumnName == "类别" || column.ColumnName == "客户名称" || column.ColumnName == "业务员")
                {
                    columnList.Add(column.ColumnName);
                }
            }
        }


        DataTableDynamicContext = new DataTableDynamicContext(dt, (context, col) =>
            {
                //所有列可排序
                col.Sortable = true;
                col.DefaultSort = true;
                 if (col.GetFieldName() == "产品名称" || col.GetFieldName() == "规格" || col.GetFieldName() == "类别" || col.GetFieldName() == "客户名称" || col.GetFieldName() == "业务员")
                {
                    col.Fixed = true;
                    // col.TextEllipsis = true;
                    //flex固定列
                }

                if (col.GetFieldName() == "业务员")
                {
                    col.Width = 60;
                }
                else if (col.GetFieldName() == "序号")
                {
                    col.Width = 40;
                    col.Fixed = true;
                }
                else if (col.GetFieldName() == "类别")
                {
                    col.Width = 40;
                }
                else if (col.GetFieldName() == "客户名称" && columnList.Count < 4)
                {
                    col.Width = 130;
                    col.TextEllipsis = true;
                }
                else if (col.GetFieldName() == "客户名称" && columnList.Count >= 4)
                {
                    col.Width = 130;
                }
                else if (col.GetFieldName() == "产品名称" && columnList.Count < 4)
                {
                    col.Width = 100;
                    col.TextEllipsis = true;
                }
                else if (col.GetFieldName() == "产品名称" && columnList.Count >= 4)
                {
                    col.Width = 100;
                }
                else
                {
                    col.Width = 80;
                }

               
                

                    // col.TextWrap = true;

                if (col.GetFieldName().Contains("月退货金额") || col.GetFieldName().Contains("月净金额") || col.GetFieldName().Contains("月发货数量") 
                    || col.GetFieldName().Contains("月退货数量") || col.GetFieldName().Contains("月净数量") || col.GetFieldName().Contains("发货数量总计") 
                    || col.GetFieldName().Contains("发货金额总计") || col.GetFieldName().Contains("月质量退货"))
                {
                      col.Visible = false;
                }

                
                // //如果列的类型是数字类型设置宽度120
                // if (Type.GetTypeCode(col.PropertyType) != TypeCode.String)
                // {
                //     col.Width = 120;
                // }
                // else
                // {
                //     col.Width = 150;
                // }
                if (col.GetFieldName() == "本期回款率" || col.GetFieldName() == "同期回款率" || col.GetFieldName() == "本同期回款率差异" || col.GetFieldName() == "退货率")
                {
                    col.FormatString = "P2";
                }
            });

        StateHasChanged();
    }
}
